package com.crazymaker.springcloud.sharding;

import org.apache.shardingsphere.api.config.masterslave.LoadBalanceStrategyConfiguration;
import org.apache.shardingsphere.api.config.masterslave.MasterSlaveRuleConfiguration;
import org.apache.shardingsphere.core.rule.MasterSlaveRule;
import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.MasterSlaveDataSource;
import org.junit.Before;
import org.junit.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class ShardingMasterSlaveJavaApiTester extends ShardingJavaApiTester {
    private static final String USER_LOGIC_TB = "t_user";
    DataSource dataSource;

    DataSource masterDataSource;

    DataSource slaveDataSource;

    /**
     * 通过ShardingDataSourceFactory 构建分片数据源
     *
     * @return
     * @throws SQLException
     */
    @Before

    public void buildShardingDataSource() throws SQLException {
        /*
         * 1. 数据源集合：dataSourceMap
         * 2. 分片规则：shardingRuleConfig
         * 3. 属性：properties
         *
         */

        masterDataSource = buildDruidDataSource(
                "jdbc:mysql://cdh2:13306/demo?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=true&serverTimezone=UTC",
                "root", "123456");

        slaveDataSource = buildDruidDataSource(
                "jdbc:mysql://cdh2:13307/demo?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=true&serverTimezone=UTC",
                "root", "123456");
        // 配置真实数据源


        Map<String, DataSource> dataSourceMap = new HashMap<>(2, 1);
        dataSourceMap.put("test_ds_master", masterDataSource);
        dataSourceMap.put("test_ds_slave", slaveDataSource);

        MasterSlaveRule masterSlaveRule = new MasterSlaveRule(
                new MasterSlaveRuleConfiguration("test_ds",
                        "test_ds_master",
                        Collections.singletonList("test_ds_slave"),
                        new LoadBalanceStrategyConfiguration("ROUND_ROBIN")));


        Properties properties = new Properties();
        //打印sql语句，生产环境关闭
        properties.setProperty("sql.show", Boolean.TRUE.toString());

        dataSource = new MasterSlaveDataSource(dataSourceMap, masterSlaveRule, properties);

    }


    // CREATE TABLE `t_user` (`user_id` bigInt NOT NULL, `name` VARCHAR(45) NULL, PRIMARY KEY (`user_id`));

    /**
     * 新增测试.
     */
    @Test
    public void testInsertUser() throws SQLException {

        /*
         * 1. 需要到DataSource
         * 2. 通过DataSource获取Connection
         * 3. 定义一条SQL语句.
         * 4. 通过Connection获取到PreparedStament.
         *  5. 执行SQL语句.
         *  6. 关闭连接.
         */


        // * 2. 通过DataSource获取Connection
        Connection connection = dataSource.getConnection();
        // * 3. 定义一条SQL语句.
        // 注意：******* sql语句中 使用的表是 上面代码中定义的逻辑表 *******
        String sql = "insert into t_user(user_id,name) values(11,'name-0001')";

        // * 4. 通过Connection获取到PreparedStament.
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // * 5. 执行SQL语句.
        preparedStatement.execute();

        sql = "insert into t_user(user_id,name) values(1,'name-0002')";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.execute();

        // * 6. 关闭连接.
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testSelectUserBetween() throws SQLException {

        /*
         * 1. 需要到DataSource
         * 2. 通过DataSource获取Connection
         * 3. 定义一条SQL语句.
         * 4. 通过Connection获取到PreparedStament.
         *  5. 执行SQL语句.
         *  6. 关闭连接.
         */


        // * 2. 通过DataSource获取Connection
        Connection connection = dataSource.getConnection();
        // * 3. 定义一条SQL语句.
        // 注意：******* sql语句中 使用的表是 上面代码中定义的逻辑表 *******
        String sql = "select * from  t_user where user_id between 10 and 20 ";

        // * 4. 通过Connection获取到PreparedStament.
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // * 5. 执行SQL语句.
        ResultSet resultSet = preparedStatement.executeQuery();


        // * 6. 关闭连接.
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testSelectUserIn() throws SQLException {

        /*
         * 1. 需要到DataSource
         * 2. 通过DataSource获取Connection
         * 3. 定义一条SQL语句.
         * 4. 通过Connection获取到PreparedStament.
         *  5. 执行SQL语句.
         *  6. 关闭连接.
         */


        // * 2. 通过DataSource获取Connection
        Connection connection = dataSource.getConnection();
        // * 3. 定义一条SQL语句.
        // 注意：******* sql语句中 使用的表是 上面代码中定义的逻辑表 *******
        String sql = "select * from  t_user where user_id in (10,11,23)";

        // * 4. 通过Connection获取到PreparedStament.
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // * 5. 执行SQL语句.
        ResultSet resultSet = preparedStatement.executeQuery();


        // * 6. 关闭连接.
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testSelectUserBigThan() throws SQLException {

        /*
         * 1. 需要到DataSource
         * 2. 通过DataSource获取Connection
         * 3. 定义一条SQL语句.
         * 4. 通过Connection获取到PreparedStament.
         *  5. 执行SQL语句.
         *  6. 关闭连接.
         */


        // * 2. 通过DataSource获取Connection
        Connection connection = dataSource.getConnection();
        // * 3. 定义一条SQL语句.
        // 注意：******* sql语句中 使用的表是 上面代码中定义的逻辑表 *******
        String sql = "select * from  t_user where user_id > 10000";

        // * 4. 通过Connection获取到PreparedStament.
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // * 5. 执行SQL语句.
        ResultSet resultSet = preparedStatement.executeQuery();


        // * 6. 关闭连接.
        preparedStatement.close();
        connection.close();
    }


}